﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Text;
using System.IO;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.VersionControl.Client;
using System.Linq;
namespace DBSchemaService.Utils
{
    public static class DatabaseHelper
    {
        internal static readonly List<StoredProcedureFileConfiguration> _storedProcConfigurations = ConfigurationHelper.GetStoredProcedureFileConfigurations();
        internal static readonly List<SchemaConfiguration> schemaConfigurations = ConfigurationHelper.GetSchemaConfigurations();
        internal static string CreateSchemaTableSQL = System.Configuration.ConfigurationManager.AppSettings["CreateSchemaTableSQL"];
        internal static readonly string _workspace = ConfigurationManager.AppSettings["workspace"];
        internal static readonly string _localPathStartDir = ConfigurationManager.AppSettings["localPathStartDir"];

        #region DB Methods
        #region DB Scripting

        public static void GenerateDatabaseScript(TeamFoundationServer tfs)
        {


            VersionControlServer sourceControl;
            Workspace[] workspaces;
            string serverPathForHistoryFile;
            string localPathForHistoryFile;
            string createClientDBSproc = null;
            string tfscreateClientDBSproc;
            Workspace workspace;

            StringBuilder stringy = new StringBuilder();

            serverPathForHistoryFile = string.Empty;
            localPathForHistoryFile = string.Empty;
            workspace = null;

            //create a workspace instance to modify *_create.sql from tfs
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());

            if (workspaces.Length >= 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();


            //get the local path and server path for each create file
            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                bool didsprocfile = false;
                localPathForHistoryFile = schemaConfig.LocalPathForHistoryFile;
                serverPathForHistoryFile = schemaConfig.TfsServerPathForHistoryFile;

                createClientDBSproc = localPathForHistoryFile.Replace(Path.GetFileName(localPathForHistoryFile), "SetupClientDatabase.sql");
                tfscreateClientDBSproc = serverPathForHistoryFile.Replace(Path.GetFileName(serverPathForHistoryFile), "SetupClientDatabase.sql");

                //check if the server path is mapped to a local path
                if (!workspace.IsServerPathMapped(serverPathForHistoryFile))
                    workspace.Map(serverPathForHistoryFile, localPathForHistoryFile);
                if (!workspace.IsServerPathMapped(tfscreateClientDBSproc))
                    workspace.Map(tfscreateClientDBSproc, createClientDBSproc);


                GetStatus status = workspace.Get();
                Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
                foreach (Conflict conflict in conflicts)
                {
                    conflict.Resolution = Resolution.OverwriteLocal;
                    workspace.ResolveConflict(conflict);
                }

                //check out the file and append the sql script to schema.sql
                workspace.PendEdit(localPathForHistoryFile);
                workspace.SetLock(localPathForHistoryFile, LockLevel.CheckOut);

                stringy = new StringBuilder();
                ServerConnection connection = new ServerConnection(new SqlConnection(schemaConfig.ConnectionString));
                Microsoft.SqlServer.Management.Smo.Server svr = new Microsoft.SqlServer.Management.Smo.Server(connection);

                try
                {
                    WriteToLogFile(string.Format("Start Generating Database Script for Dev at {0}: {1}", DateTime.Now, schemaConfig.Metadata));
                    stringy = GenerateDBScript(svr, schemaConfig.Metadata);
                    File.WriteAllText(localPathForHistoryFile, stringy.ToString());

                    if (!String.IsNullOrEmpty(createClientDBSproc) && schemaConfig.Metadata.Equals("clientdb",StringComparison.InvariantCultureIgnoreCase))
                    {
                        didsprocfile = true;
                        workspace.PendEdit(createClientDBSproc);
                        workspace.SetLock(createClientDBSproc, LockLevel.CheckOut);
                        WriteToLogFile(string.Format("Start Generating SPROC Script for Dev at {0}: {1}", DateTime.Now, schemaConfig.Metadata));
                        // we need to generate the sproc for client DB creation
                        StringBuilder tempBuilder = GenerateCreateClientStoredProc(svr, schemaConfig.Metadata);
                        File.WriteAllText(createClientDBSproc, tempBuilder.ToString());
                    }
                    else
                    {
                        WriteToLogFile(string.Format("Skipped Generating SPROC Script for Dev at {0}: {1}", DateTime.Now, schemaConfig.Metadata));
                    }
                }
                catch (Exception ex)
                {
                    using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
                    {
                        sw.WriteLine(string.Format("DATE - {0}", DateTime.Now));
                        sw.WriteLine("EXCEPTION - error during DB scripting process");
                        sw.WriteLine("DATABASE: " + schemaConfig.Database);
                        sw.WriteLine("CONNECTION STRING: " + schemaConfig.ConnectionString);
                        sw.WriteLine("LOCAL PATH FOR HISTORY FILE: " + localPathForHistoryFile);
                        sw.WriteLine("     Message: " + ex.Message);
                        sw.WriteLine("     Source: " + ex.Source);
                        sw.WriteLine("     StackTrace: " + ex.StackTrace);

                        if (ex.InnerException != null)
                        {
                            sw.WriteLine("     Inner Exception");
                            sw.WriteLine("          Message: " + ex.InnerException.Message);
                            sw.WriteLine("          Source: " + ex.InnerException.Source);
                            sw.WriteLine("          StackTrace: " + ex.InnerException.StackTrace);
                        }
                    }
                }

                //get pending change and check it in to TFS

                List<string> innerChanges = new List<string>();
                innerChanges.Add(localPathForHistoryFile);
                if (didsprocfile)
                {
                    innerChanges.Add(createClientDBSproc);
                }

                PendingChange[] changes = workspace.GetPendingChanges(innerChanges.ToArray());

                //check-in to TFS
                if (changes.Length > 0)
                    workspace.CheckIn(changes, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***");

                //why do we check it out again here? - to keep it from being changed by anyone other than this process.
                workspace.SetLock(localPathForHistoryFile, LockLevel.CheckOut);

                if (!String.IsNullOrEmpty(createClientDBSproc))
                {
                    workspace.SetLock(createClientDBSproc, LockLevel.CheckOut);
                }
            }
        }

        public static void GenerateDatabaseScript(TeamFoundationServer tfs, string branch)
        {
            VersionControlServer sourceControl;
            Workspace[] workspaces;
            string serverPathForHistoryFile;
            string localPathForHistoryFile;
            string createClientDBSproc;
            string tfscreateClientDBSproc;
            Workspace workspace;
            double result = 0;

            StringBuilder stringy = new StringBuilder();

            serverPathForHistoryFile = string.Empty;
            localPathForHistoryFile = string.Empty;
            workspace = null;

            //create a workspace instance to modify *_create.sql from tfs
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());

            if (workspaces.Length >= 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();


            //get the local path and server path for each create file
            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                bool didsprocfile = false;
                if ((double.TryParse(branch, out result)))
                    serverPathForHistoryFile = schemaConfig.TfsServerPathForHistoryFile.Replace("Dev", string.Format("Releases/{0}/{1}", branch, branch));
                else
                    serverPathForHistoryFile = schemaConfig.TfsServerPathForHistoryFile.Replace("Dev", branch);

                localPathForHistoryFile = schemaConfig.LocalPathForHistoryFile.Replace("dev", branch);

                createClientDBSproc = localPathForHistoryFile.Replace(Path.GetFileName(localPathForHistoryFile), "MAIN.SetupClientDatabase.sql");
                tfscreateClientDBSproc = serverPathForHistoryFile.Replace(Path.GetFileName(serverPathForHistoryFile), "MAIN.SetupClientDatabase.sql");

                //check if the server path is mapped to a local path
                if (!workspace.IsServerPathMapped(serverPathForHistoryFile))
                    workspace.Map(serverPathForHistoryFile, localPathForHistoryFile);
                if (!workspace.IsServerPathMapped(tfscreateClientDBSproc))
                    workspace.Map(tfscreateClientDBSproc, createClientDBSproc);


                GetStatus status = workspace.Get();
                Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
                foreach (Conflict conflict in conflicts)
                {
                    conflict.Resolution = Resolution.OverwriteLocal;
                    workspace.ResolveConflict(conflict);
                }

                //check out the file and append the sql script to schema.sql
                int num = workspace.PendEdit(localPathForHistoryFile);
                workspace.SetLock(localPathForHistoryFile, LockLevel.CheckOut);

                stringy = new StringBuilder();
                ServerConnection connection = new ServerConnection(new SqlConnection(schemaConfig.ConnectionString));
                Microsoft.SqlServer.Management.Smo.Server svr = new Microsoft.SqlServer.Management.Smo.Server(connection);

                try
                {
                    WriteToLogFile(string.Format("Start Generating Database Script for Branch {1}, Database {0}", branch, schemaConfig.Metadata));
                    stringy = GenerateDBScriptFromFiles(tfs, sourceControl, workspace, schemaConfig.Database, branch);
                    File.WriteAllText(localPathForHistoryFile, stringy.ToString());

                    if (!String.IsNullOrEmpty(createClientDBSproc) && schemaConfig.Metadata.Equals("clientdb", StringComparison.InvariantCultureIgnoreCase))
                    {
                        didsprocfile = true;
                        workspace.PendEdit(createClientDBSproc);
                        workspace.SetLock(createClientDBSproc, LockLevel.CheckOut);
                        WriteToLogFile(string.Format("Start Generating SPROC Script for Branch {1}, Database {0}", branch, schemaConfig.Metadata));
                        // we need to generate the sproc for client DB creation
                        StringBuilder tempBuilder = GenerateCreateClientStoredProc(svr, schemaConfig.Metadata);
                        File.WriteAllText(createClientDBSproc, tempBuilder.ToString());
                    }
                    else
                    {
                        WriteToLogFile(string.Format("Skipped Generating SPROC Script for Branch {1}, Database {0}", branch, schemaConfig.Metadata));
                    }
                }
                catch (Exception ex)
                {
                    LogDBCreateScriptError(localPathForHistoryFile, schemaConfig, ex);
                }

                List<string> innerChanges = new List<string>();
                innerChanges.Add(localPathForHistoryFile);
                if (didsprocfile)
                {
                    innerChanges.Add(createClientDBSproc);
                }

                PendingChange[] changes = workspace.GetPendingChanges(innerChanges.ToArray());

                //check-in to TFS
                if (changes.Length > 0)
                    workspace.CheckIn(changes, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***");

                //why do we check it out again here? - to keep it from being changed by anyone other than this process.
                workspace.SetLock(localPathForHistoryFile, LockLevel.CheckOut);
            }
        }

        private static StringBuilder GenerateDBScriptFromFiles(TeamFoundationServer tfs, VersionControlServer sourceControl, Workspace workspace, string database, string branch)
        {
            StringBuilder stringy = new StringBuilder();

            //GetStructure From Dev/Create script from the original structure file
            stringy = GetDevStructure(workspace, database);

            //Get SchemaChanges File from Branch
            stringy.AppendLine(GetSchemaChangesFile(tfs, sourceControl, workspace, branch, database).ToString());

            //Get Sprocs File from Branch
            stringy.AppendLine(GetSprocsFile(branch, database).ToString());

            return stringy;
        }

        private static StringBuilder GetDevStructure(Workspace workspace, string db)
        {
            string serverPath = string.Empty, localPath = string.Empty;

            //get the file contents 
            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                if (db.ToLower() == schemaConfig.Database.ToLower())
                {
                    serverPath = schemaConfig.TFSServerPath;
                    localPath = schemaConfig.LocalPath.Value;
                }
            }
            if (!workspace.IsServerPathMapped(serverPath))
                workspace.Map(serverPath, localPath);

            GetStatus status = workspace.Get();
            Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
            foreach (Conflict conflict in conflicts)
            {
                conflict.Resolution = Resolution.OverwriteLocal;
                workspace.ResolveConflict(conflict);
            }

            return new StringBuilder(File.ReadAllText(localPath));
        }

        private static StringBuilder GetSchemaChangesFile(TeamFoundationServer tfs, VersionControlServer sourceControl, Workspace workspace, string branchName, string databaseName)
        {
            string serverPath = string.Empty, localPath = string.Empty, serverPathRoot = string.Empty, localPathRoot = string.Empty, projectName = string.Empty;
            double result = 0;

            //get the file contents 
            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                if (databaseName.ToLower() == schemaConfig.Database.ToLower())
                {
                    if ((double.TryParse(branchName, out result)))
                        serverPathRoot = string.Format(schemaConfig.ServerPathRoot, string.Format("Releases/{0}/{1}", branchName, branchName));
                    else if (branchName.ToLower() == "dev-2.7")
                        serverPathRoot = string.Format(schemaConfig.ServerPathRoot, string.Format("Development/{0}", branchName));
                    else
                        serverPathRoot = string.Format(schemaConfig.ServerPathRoot, branchName);

                    localPathRoot = schemaConfig.LocalPathRoot;
                    projectName = schemaConfig.Project;
                    break;
                }
            }

            GetWorkspaceForSchemas(tfs, out sourceControl, out workspace, branchName, databaseName, projectName);
            workspace.Get();

            localPath = Path.Combine(localPathRoot, string.Format("{0}_schemachanges.sql", databaseName));
            serverPath = Path.Combine(serverPathRoot, string.Format("{0}_schemachanges.sql", databaseName));

            return new StringBuilder(File.ReadAllText(localPath));
        }

        private static StringBuilder GetSprocsFile(string branch, string database)
        {
            string serverPath = string.Empty, localPath = string.Empty;
            StringBuilder stringy = new StringBuilder();


            foreach (StoredProcedureFileConfiguration sprocConfig in _storedProcConfigurations)
            {
                if(sprocConfig.TFSServerPath.Value.ToLower().Contains(branch.ToLower())) 
                {
                    //get the local and server paths for the sprocs
                    serverPath = sprocConfig.TFSServerPath.Value;
                    localPath = sprocConfig.LocalPath.Value;
                }
            }

            return new StringBuilder(File.ReadAllText(localPath));
        }

        private static void LogDBCreateScriptError(string localPathForHistoryFile, SchemaConfiguration schemaConfig, Exception ex)
        {
            using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
            {
                sw.WriteLine(string.Format("DATE - {0}", DateTime.Now));
                sw.WriteLine("EXCEPTION - error during DB scripting process");
                sw.WriteLine("DATABASE: " + schemaConfig.Database);
                sw.WriteLine("CONNECTION STRING: " + schemaConfig.ConnectionString);
                sw.WriteLine("LOCAL PATH FOR HISTORY FILE: " + localPathForHistoryFile);
                sw.WriteLine("     Message: " + ex.Message);
                sw.WriteLine("     Source: " + ex.Source);
                sw.WriteLine("     StackTrace: " + ex.StackTrace);

                if (ex.InnerException != null)
                {
                    sw.WriteLine("     Inner Exception");
                    sw.WriteLine("          Message: " + ex.InnerException.Message);
                    sw.WriteLine("          Source: " + ex.InnerException.Source);
                    sw.WriteLine("          StackTrace: " + ex.InnerException.StackTrace);
                }
            }
        }

        private static StringBuilder GenerateDBScript(Microsoft.SqlServer.Management.Smo.Server myServer, string dbName)
        {
            StringBuilder theScript = new StringBuilder();
            Microsoft.SqlServer.Management.Smo.Database theDB = myServer.Databases[dbName];

            theScript.AppendLine("SET NOCOUNT ON");
            theScript.AppendLine("GO");

            GenerateSchemasAndFileGroups(theScript, theDB);
            GenerateUserDefinedTypesAndFunctions(theScript, theDB);
            GenerateTablesWithData(theScript, theDB);
            GenerateViewsSprocsAndTriggers(theScript, theDB);

            theScript.AppendLine("SET NOCOUNT OFF");
            theScript.AppendLine("GO");

            return theScript;
        }

        private static StringBuilder GenerateCreateClientStoredProc(Microsoft.SqlServer.Management.Smo.Server myServer, string dbName)
        {
            StringBuilder theScript = new StringBuilder();
            Microsoft.SqlServer.Management.Smo.Database theDB = myServer.Databases[dbName];

            theScript.Append(@"set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF EXISTS (	SELECT	name 
			FROM	sys.objects 
			WHERE	name = N'SetupClientDatabase' 
			
			AND		type = 'P')
    DROP PROCEDURE SetupClientDatabase
GO

CREATE PROCEDURE SetupClientDatabase

AS
BEGIN
    SET NOCOUNT ON
    DECLARE @createDatabaseScript nvarchar(max)
    DECLARE @params nvarchar(max)
    declare @db_name varchar(100) 
	SELECT @db_name=DB_NAME()
    SET @params = '@sourceDb varchar(100)'

    SELECT @createDatabaseScript = '");

            StringBuilder innerScript = new StringBuilder();

            GenerateSchemasAndFileGroups(innerScript, theDB);
            GenerateUserDefinedTypesAndFunctions(innerScript, theDB);
            GenerateTablesWithData(innerScript, theDB);
            GenerateViewsSprocsAndTriggers(innerScript, theDB);

            innerScript.Replace("'", "''");
            theScript.Append(innerScript.ToString());
            theScript.AppendLine("'");

            theScript.AppendLine(@"
EXEC sys.sp_executesql @createDatabaseScript, @params, @sourceDb=@db_name

SET NOCOUNT OFF

END --End of Procedure

GO

DENY EXECUTE ON SetupClientDatabase to public
go");            
            
            return theScript;
        }

        private static void GenerateSchemasAndFileGroups(StringBuilder theScript, Microsoft.SqlServer.Management.Smo.Database theDB)
        {
            theScript.AppendLine("-- schemas and filegroups start ");
            ScriptingOptions options = GetBasicScriptingOptions();
            options.NoCollation = true;
            string filename = string.Empty;

            /* Generating CREATE SCHEMAS with IF EXISTS for non-system objects*/
            foreach (Schema schema in theDB.Schemas)
                if (!schema.IsSystemObject)
                    foreach (string s in schema.Script(options))
                        theScript.AppendLine(s);

            foreach (FileGroup fg in theDB.FileGroups)
            {
                if (fg.Name != "PRIMARY")
                {
                    //theScript.AppendLine("if NOT EXISTS(select * from sys.filegroups WHERE name='" + theDB.Name + "')\r\nBEGIN");
                    //theScript.AppendLine("ALTER DATABASE " + theDB.Name + " ADD FILEGROUP " + fg.Name + " CONTAINS FILESTREAM\r\nEND\r\nGO");
                    // this works on the CURRENT database"
                    theScript.AppendLine(@"if NOT EXISTS(select * from sys.filegroups WHERE type='FD')
BEGIN
	declare @sqlstatement1 varchar(500)
	select @sqlstatement1='ALTER DATABASE '+DB_NAME()+' ADD FILEGROUP " + fg.Name + @" CONTAINS FILESTREAM'
	EXEC(@sqlstatement1)
END");

                    foreach (DataFile file in fg.Files)
                    {
                        filename = file.FileName.Substring(file.FileName.LastIndexOf('\\') + 1);

                        theScript.AppendLine("declare @filepath varchar(500), @sqlstatement varchar(500)");
                        theScript.AppendLine("select @filepath=reverse(SUBSTRING(REVERSE(physical_name), charindex('\\',REVERSE(physical_name),0),1000)) + DB_NAME()  from sys.database_files where file_id=1");
                        theScript.AppendLine("select @sqlstatement='ALTER DATABASE '+DB_NAME()+' ADD FILE ( NAME=''" + file.Name + "'', FILENAME='''+@filepath+''' ) TO FILEGROUP " + fg.Name + "'");
                        theScript.AppendLine("EXEC(@sqlstatement)");
                    }
                }
            }
            theScript.AppendLine("-- schemas and filegroups end ");
        }

        private static void GenerateUserDefinedTypesAndFunctions(StringBuilder theScript, Microsoft.SqlServer.Management.Smo.Database theDB)
        {
            theScript.AppendLine("-- user defined types and functions start");

            ScriptingOptions options = GetBasicScriptingOptions();
            options.NoCollation = true;
            foreach (UserDefinedType type in theDB.UserDefinedTypes)
                foreach (string s in type.Script(options))
                    theScript.AppendLine(s);

            foreach (UserDefinedDataType type in theDB.UserDefinedDataTypes)
                foreach (string s in type.Script(options))
                    theScript.AppendLine(s);

            foreach (UserDefinedTableType type in theDB.UserDefinedTableTypes)
                if (type.IsUserDefined)
                    foreach (string s in type.Script(options))
                        theScript.AppendLine(s);

            foreach (UserDefinedAggregate ag in theDB.UserDefinedAggregates)
                foreach (string s in ag.Script(options))
                    theScript.AppendLine(s);

            foreach (UserDefinedFunction func in theDB.UserDefinedFunctions)
                if (!func.IsSystemObject)
                    foreach (string s in func.Script(options))
                        theScript.AppendLine(s);

            theScript.AppendLine("-- user defined types and functions end");
        }

        private static void GenerateTablesWithData(StringBuilder theScript, Microsoft.SqlServer.Management.Smo.Database theDB)
        {
            theScript.AppendLine("-- tables with data start ");
            ScriptingOptions scriptOptionsNoData = GetBasicScriptingOptions();
            scriptOptionsNoData.NoCollation = true;

            ScriptingOptions scriptOptionsData = GetBasicScriptingOptions();
            scriptOptionsData.NoCollation = true;

            // *** TABLES w/ DATA ***
            scriptOptionsData.ScriptData = true;
            scriptOptionsData.Indexes = true;

            foreach (Table myTable in theDB.Tables)
            {
                if (!myTable.IsSystemObject)// && myTable.Name.ToLower() == "fileindex")
                {
                    //need to exclude certain tables for now.
                    if (myTable.Name.ToLower() == "dateindex" || myTable.Name.ToLower() == "dimdate")
                        scriptOptionsData.ScriptData = false;
                    else
                        scriptOptionsData.ScriptData = true;

                    /* Generating CREATE TABLE command for TABLES with IF EXISTS and DATA */
                    foreach (string s in myTable.EnumScript(scriptOptionsData))
                        theScript.AppendLine(s);

                    /* Generating DEFAULT CONSTRAINTS commands */
                    foreach (Column col in myTable.Columns)
                        if (col.DefaultConstraint != null)
                            foreach (string s in col.DefaultConstraint.Script())
                                theScript.AppendLine(s);

                }
            }

            //Script foreign keys after all tables have been created.
            foreach (Table myTable in theDB.Tables)
            {
                if (!myTable.IsSystemObject)// && myTable.Name.ToLower() == "fileindex")
                {
                    /* Generating FOREIGN KEY commands */
                    ScriptingOptions o = new ScriptingOptions();
                    o.SchemaQualifyForeignKeysReferences = true;
                    o.DriAll = true;

                    foreach (ForeignKey key in myTable.ForeignKeys)
                        foreach (string s in key.Script(o))
                            theScript.AppendLine(s);

                }
            }

            theScript.AppendLine("-- tables with data end ");
        }

        private static void GetWorkspaceForSchemas(TeamFoundationServer tfs, out VersionControlServer sourceControl, out Workspace workspace, string branch, string db, string projectName)
        {
            double result = 0;
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            Workspace[] workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());
            workspace = null;
            if (workspaces.Length == 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
            {
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();
            }
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
            {
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            }

            //set up folder mapping before checkins
            if (!Directory.Exists(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
                Directory.CreateDirectory(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

            if (!workspace.IsLocalPathMapped(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
            {
                if (!double.TryParse(branch, out result)) //check if the branch is a number, which will be a release in the Releases Folder
                    workspace.Map(string.Format("$/California/{0}/{1}/Database/{2}/Schemas", projectName, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                else
                    workspace.Map(string.Format("$/California/{0}/Releases/{1}/{2}/Database/{3}/Schemas", projectName, branch, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

            }
        }

        private static void GenerateViewsSprocsAndTriggers(StringBuilder theScript, Microsoft.SqlServer.Management.Smo.Database theDB)
        {
            theScript.AppendLine("-- views sprocs triggers start ");
            ScriptingOptions options = GetBasicScriptingOptions();
            options.NoCollation = true;

            foreach (View myView in theDB.Views)
                if (!myView.IsSystemObject)
                    foreach (string s in myView.Script(options))
                        theScript.AppendLine(s);

            foreach (StoredProcedure sproc in theDB.StoredProcedures)
                if (!sproc.IsSystemObject)
                    foreach (string s in sproc.Script(options))
                        theScript.AppendLine(s);

            // database level triggers
            foreach (DatabaseDdlTrigger trig in theDB.Triggers)
                if (!trig.IsSystemObject)
                    foreach (string s in trig.Script(options))
                        theScript.AppendLine(s);

            // table level triggers
            foreach (Table tab in theDB.Tables)
            {
                if (tab.IsSystemObject)
                {
                    continue;
                }
                foreach (Trigger trig in tab.Triggers)
                {
                    if (trig.IsSystemObject)
                    {
                        continue;
                    }
                    foreach (string s in trig.Script(options))
                    {
                        theScript.AppendLine(s);
                    }

                }
            }

            theScript.AppendLine("-- views sprocs triggers end ");
        }

        private static ScriptingOptions GetBasicScriptingOptions()
        {
            /* With ScriptingOptions you can specify different scripting
            * options, for example to include IF NOT EXISTS, DROP
            * statements, output location etc*/
            ScriptingOptions optns = new ScriptingOptions();
            optns.ScriptDrops = false;
            optns.IncludeIfNotExists = true;
            optns.SchemaQualify = true;
            optns.ScriptSchema = true;
            optns.ScriptData = false;

            return optns;
        }
        #endregion

        public static bool IsView(string sql)
        {
            if (sql.ToLower().Contains("create view"))
                return true;
            return false;
        }

        public static string WrapSQLInExecStatement(string sql)
        {
            return string.Format("{0} {1} {2}", @"EXEC('", sql, @"')");
        }

        public static string MakeSQLSafe(string sql)
        {
            return sql.Replace("'", "''");
        }

        public static void UpdateSchemaVersion(int versionNumber, string database)
        {
            string dbSchemaConnectionString = GetSchemaConnectionString(database);

            using (SqlConnection mySqlConnection = new SqlConnection(dbSchemaConnectionString))
            {
                using (SqlCommand mySqlCommand = mySqlConnection.CreateCommand())
                {
                    try
                    {
                        string updateVersionQuery = string.Empty;
                        foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
                        {
                            if (schemaConfig.Database.ToLower() == database.ToLower())
                            {
                                updateVersionQuery = schemaConfig.UpdateVersionQuery;
                                break;
                            }
                        }

                        updateVersionQuery = updateVersionQuery.Replace("@", versionNumber.ToString());
                        mySqlCommand.CommandText = updateVersionQuery;
                        mySqlConnection.Open();
                        mySqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                    }
                }
            }
        }

        public static void UpdateSchChangesTable(string query, string database)
        {
            WriteToLogFile(string.Format("Start SCH.Changes Table Update for {0}", database));
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            using (DbConnection connectionObject = factory.CreateConnection())
            {
                string dbSchemaConnectionString = GetSchemaConnectionString(database);
                connectionObject.ConnectionString = dbSchemaConnectionString;
                connectionObject.Open();
                try
                {
                    SqlConnection connection = new SqlConnection(dbSchemaConnectionString);
                    Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(connection));
                    server.ConnectionContext.ExecuteNonQuery(query);
                }
                catch (Exception ex)
                {
                    WriteToLogFile(ex.Message.ToString());                   
                }
                finally
                {
                    connectionObject.Close();
                }
            }
            WriteToLogFile("Finished SCH.Changes Table Update");
        }





        public static void UpdateSchemaVersion(double versionNumber, string database, string branch, string metadata)
        {
            string dbSchemaConnectionString = GetSchemaConnectionString(database);

            using (SqlConnection mySqlConnection = new SqlConnection(dbSchemaConnectionString))
            {
                using (SqlCommand mySqlCommand = mySqlConnection.CreateCommand())
                {
                    try
                    {
                        string updateVersionQuery = string.Empty;
                        foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
                        {
                            if (schemaConfig.Database.ToLower() == database.ToLower())
                            {
                                updateVersionQuery = schemaConfig.UpdateVersionQuery;
                                break;
                            }
                        }

                        updateVersionQuery = string.Format(updateVersionQuery, versionNumber, metadata, branch);

                        mySqlCommand.CommandText = updateVersionQuery;
                        mySqlConnection.Open();
                        mySqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                    }
                }
            }
        }

        public static string GetSchemaConnectionString(string database)
        {
            string dbSchemaConnectionString = string.Empty;
            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                if (schemaConfig.Database.ToLower() == database.ToLower())
                {
                    dbSchemaConnectionString = schemaConfig.ConnectionString;
                    break;
                }
            }
            return dbSchemaConnectionString;
        }

        public static int GetCurrentSchemaVersion(string database)
        {
            int schemaVersion = 0;
            string getVersionQuery = string.Empty;
            string dbSchemaConnectionString = GetSchemaConnectionString(database);

            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                if (schemaConfig.Database.ToLower() == database.ToLower())
                {
                    getVersionQuery = schemaConfig.SelectVersionQuery;
                    break;
                }
            }

            using (SqlConnection mySqlConnection = new SqlConnection(dbSchemaConnectionString))
            {
                using (SqlCommand mySqlCommand = mySqlConnection.CreateCommand())
                {
                    try
                    {
                        mySqlCommand.CommandText = getVersionQuery;
                        mySqlConnection.Open();
                        schemaVersion = (int)mySqlCommand.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                        if (ex.Message.Contains("Invalid object name"))
                        {
                            mySqlCommand.CommandText = CreateSchemaTableSQL;
                            mySqlCommand.BeginExecuteNonQuery();
                            GetCurrentSchemaVersion(database);
                        }
                    }
                }
            }
            return schemaVersion;
        }

        public static double GetCurrentSchemaVersion(string database, string metadata, string branch)
        {
            double schemaVersion = 0;
            string getVersionQuery = string.Empty;
            string dbSchemaConnectionString = GetSchemaConnectionString(database);

            foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
            {
                if (schemaConfig.Database.ToLower() == database.ToLower())
                {
                    getVersionQuery = schemaConfig.SelectVersionQuery;
                    break;
                }
            }

            using (SqlConnection mySqlConnection = new SqlConnection(dbSchemaConnectionString))
            {
                using (SqlCommand mySqlCommand = mySqlConnection.CreateCommand())
                {
                    try
                    {
                        mySqlCommand.CommandText = string.Format(getVersionQuery, metadata, branch);
                        mySqlConnection.Open();
                        schemaVersion = Double.Parse(mySqlCommand.ExecuteScalar().ToString());
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice_log.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                        if (ex.Message.Contains("Invalid object name"))
                        {
                            mySqlCommand.CommandText = CreateSchemaTableSQL;
                            mySqlCommand.BeginExecuteNonQuery();
                            GetCurrentSchemaVersion(database, metadata, branch);
                        }
                    }
                }
            }
            return schemaVersion;
        }

        /// <summary>
        /// Run SQL against DB, return whether it is valid or not, and update Info field       
        /// </summary>
        public static bool ApplyDBChanges(string f, WorkItem wi)
        {
            string script = string.Empty;
            using (StreamReader s = new StreamReader(f))
            {
                script = s.ReadToEnd();
            }

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            using (DbConnection connectionObject = factory.CreateConnection())
            {
                string dbSchemaConnectionString = GetSchemaConnectionString(wi.Fields["Database"].Value.ToString());
                connectionObject.ConnectionString = dbSchemaConnectionString;
                connectionObject.Open();
                DbTransaction transactionObject = connectionObject.BeginTransaction();
                try
                {
                    DbCommand command = connectionObject.CreateCommand();
                    command.Transaction = transactionObject;
                    command.CommandText = script;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                    transactionObject.Commit();
                    wi.History += String.Format("<p>[{0} {1}] SUCCESS: The Schema change ran successfully. </p>", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
                }
                catch (Exception ex)
                {
                    wi.History += String.Format("<p>[{0} {1}] ERROR: {2} </p>", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message);
                    transactionObject.Rollback();
                    return false;
                }
                finally
                {
                    connectionObject.Close();
                }
            }


            return true;
        }
        
        /// <summary>
        /// Run Sprocs against DB, return whether they are valid or not
        /// </summary>
        /// <param name="sqlToModifySchema"></param>
        /// <param name="wi"></param>
        /// <returns></returns>
        public static bool ApplyDBChanges(string sprocs, string database)
        {
            WriteToLogFile(string.Format("Start Proc Execution on TFS Server for {0}",database));
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            using (DbConnection connectionObject = factory.CreateConnection())
            {
                string dbSchemaConnectionString = GetSchemaConnectionString(database);
                connectionObject.ConnectionString = dbSchemaConnectionString;
                connectionObject.Open();
                try
                {
                    using (SqlConnection connection = new SqlConnection(dbSchemaConnectionString))
                    {
                        Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(connection));
                        server.ConnectionContext.ExecuteNonQuery(sprocs);
                    }
                }
                catch (ExecutionFailureException fail)
                {
                    WriteToLogFile("Proc Execution Failed");
                    WriteToLogFile(fail.ToString());
                    if (fail.InnerException != null)
                    {
                        WriteToLogFile(fail.InnerException.ToString());
                    }
                }
                catch (Exception ex)
                {
                    WriteToLogFile(ex.Message.ToString());
                    return false;
                }
            }
            WriteToLogFile("Finished Proc Execution on TFS Server");
            return true;
        }





        private static void WriteToLogFile(string text)
        {
            using (StreamWriter sw = new StreamWriter(@"C:\Logs\schemaservice.log", true))
            {
                sw.WriteLine(text);
            }
        }
      
        #endregion
    }
}
